


ALTER         proc sp_release_all_lineitems

@customer_po varchar(50), @product_id int, @customer_id int, @user_id varchar(20),
@user_name varchar(50), @user_group varchar(50)

as

declare @salesOrder_id int
declare @lineItem_id int

declare @pCursor CURSOR
if @customer_po is not null
begin
	begin
	set @pCursor = CURSOR FOR
	SELECT Sales_Order.SalesOrder_ID, LineItem_ID FROM Customer_Open_Order_LineItem, 
	Sales_Order WHERE SO_Customer_PO_Number = @customer_po and COOL_Status = 'HOLD'
	and Sales_Order.SalesOrder_ID = Customer_Open_Order_LineItem.SalesOrder_ID
	end
end
else
begin	
     if((@customer_id <> '0')	and (@product_id <> '0'))
  	begin
	set @pCursor = CURSOR FOR
	SELECT Sales_Order.SalesOrder_ID, LineItem_ID FROM Sales_Order, 
	Customer_Open_Order_LineItem WHERE
	Customer_ID = @customer_id and	Product_ID = @product_id
	and COOL_Status = 'HOLD'
	and Sales_Order.SalesOrder_ID = Customer_Open_Order_LineItem.SalesOrder_ID	
	end
     else if((@customer_id = '0') and (@product_id <> '0'))
	begin
	set @pCursor = CURSOR FOR
	SELECT Sales_Order.SalesOrder_ID, LineItem_ID FROM Sales_Order, 
	Customer_Open_Order_LineItem WHERE
	Product_ID = @product_id and COOL_Status = 'HOLD'
	and Sales_Order.SalesOrder_ID = Customer_Open_Order_LineItem.SalesOrder_ID	
	end
     else if((@customer_Id <> '0') and (@product_id = '0'))
	begin
	set @pCursor = CURSOR FOR
	SELECT Sales_Order.SalesOrder_ID, LineItem_ID FROM Sales_Order, 
	Customer_Open_Order_LineItem WHERE
	Customer_ID = @customer_id and COOL_Status = 'HOLD'
	and Sales_Order.SalesOrder_ID = Customer_Open_Order_LineItem.SalesOrder_ID	
	end
end
OPEN @pCursor
	
FETCH next FROM @pCursor INTO @salesOrder_id, @lineItem_id 
WHILE @@fetch_status = 0
begin
	
	/*
	 * Execute proc for holding the lineItem, 0 for hold
	 */
	exec sp_approve_lineItem @lineItem_id, @salesOrder_id, @user_id, @user_name,
		@user_group, 1

	FETCH next FROM @pCursor INTO @salesOrder_id, @lineItem_id 		
end
	close @pCursor
	deallocate @pCursor


